Presentation

Model Layer

Accessing External Resources
Chapter 8 of "The definitive guide to Symfony"
Chapter 3 of "Patterns of Enterprise Application Architecture

Why?

Good to separate Database Access from the domain logic
Two approaches for gateways: Row Data Gateway and Table Data Gateway
Behavioral Issues: where to download what? (foreign keys, etc., simultaneous reading)
Advantage of Using Metadata (new names for fields, etc.)
Database Connection: keep it alive

Business Logic Relies on Data Model

ORM

Reusability
New Accessors can be defined
Database Abstraction (Propel as ORM and Creole as Database Abstraction)
Database Schema
Model Classes

We need a description of the model

propel:
_attributes:
package: lib.model
defaultIdMethod: native
phones:
_attributes: { phpName: Phones }
id: { type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }
fname: { type: VARCHAR, size: '255', required: true }
lname: { type: VARCHAR, size: '255', required: true }
name: { type: VARCHAR, size: '255' }
number: { type: VARCHAR, size: '255', required: true }
created_at: { type: TIMESTAMP }
students:
_attributes: { phpName: Students }
name: { type: VARCHAR, size: '255', required: true }
created_at: { type: TIMESTAMP, required: true }
id: { type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }

Method 1

symfony propel:build-model
symfony propel:build-sql
//then use the sql file to create appropriate tables

Method 2

//you already have your database set up
symfony propel:build-schema
symfony propel:build-model

Result

Phones.php in lib/model
BasePhones.php in lib/model/om
PhonesPeer.php in lib/model
BasePhonesPeer.php in lib/model/om
PhoneMapBuilder.php in lib/model/map

New in Symfony 1.2

symfony propel:build-filters
symfony propel:build-forms

Results in

PhonesFormFilter.class.php in lib/model/forms
PhonesForm.class.php in lib/model/filters

$c = new Criteria();
$c->add(UsersPeer::EMAIL, '%safari%', Criteria::LIKE);
$c->addAscendingOrderByColumn(UsersPeer::CREATED_AT);
$users = CommentPeer::doSelect($c);

Methods

$users = UsersPeer::doSelect($c)
$user = UsersPeer::doSelectOne($c)
$user = UsersPeer::retrieveByPk($id)
$users = UsersPeer::retrieveByPks($id_array)

Building Criteria

See this reference

SQL	Criteria
WHERE column = value	->add(column, value);
WHERE column <> value	->add(column, value, Criteria::NOT_EQUAL);
Other Comparison Operators
> , <	Criteria::GREATER_THAN, Criteria::LESS_THAN
>=, <=	Criteria::GREATER_EQUAL, Criteria::LESS_EQUAL
IS NULL, IS NOT NULL	Criteria::ISNULL, Criteria::ISNOTNULL
LIKE, ILIKE	Criteria::LIKE, Criteria::ILIKE
IN, NOT IN	Criteria::IN, Criteria::NOT_IN
Other SQL Keywords
ORDER BY column ASC	->addAscendingOrderByColumn(column);
ORDER BY column DESC	->addDescendingOrderByColumn(column);
LIMIT limit	->setLimit(limit)
OFFSET offset	->setOffset(offset)
FROM table1, table2 WHERE table1.col1 = table2.col2	->addJoin(col1, col2)
FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2	->addJoin(col1, col2, Criteria::LEFT_JOIN)
FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2	->addJoin(col1, col2, Criteria::RIGHT_JOIN)

Raw SQL

$connection = Propel::getConnection();
$query = 'SELECT MAX(%s) AS max FROM %s';
$query = sprintf($query, ArticlePeer::CREATED_AT, ArticlePeer::TABLE_NAME);
$statement = $connection->prepareStatement($query);
$resultset = $statement->executeQuery();
$resultset->next();
$max = $resultset->getInt('max');

$user->save();
$user->delete();